package com.dmx.cep.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.dmx.cep.entity.Channel;
import com.dmx.cep.entity.ChannelType;
import com.dmx.cep.entity.ConfAttributeSelection;
import com.dmx.cep.entity.TvContentInfo;
import com.dmx.cep.entity.TvContentStatus;
import com.dmx.cep.entity.TvContentType;
import com.dmx.fw.dao.hibernateimpl.BaseDaoImpl;

public class EpgDaoImpl extends BaseDaoImpl implements com.dmx.cep.dao.EpgDao {
	
	public List<ChannelType> getChannelTypeList(){
		return this.getHibernateTemplate().find("from ChannelType t where t.flag=1");
	}

	@Override
	public boolean isChannelCodeExists(String code) throws Exception{
		// TODO Auto-generated method stub
		List<Long> result=this.getHibernateTemplate().find("select count(*) from Channel c where c.code='"+code.trim()+"'");
		
		if(result.get(0).longValue()>0)
			return true;
		return false;
	}

	@Override
	public boolean isChannelIdExists(Long channelId) throws Exception{
		// TODO Auto-generated method stub
		List<Long> result=this.getHibernateTemplate().find("select count(*) from Channel c where c.channelId="+channelId);
		if(result.get(0).longValue()>0)
			return true;
		return false;
	}

	@Override
	public boolean isChannelNameExists(String name) throws Exception{
		// TODO Auto-generated method stub
		List<Long> result=this.getHibernateTemplate().find("select count(*) from Channel c where c.name='"+name.trim()+"'");
		
		if(result.get(0).longValue()>0)
			return true;
		return false;
	}

	@Override
	public TvContentType getContentTypeByName(String typeName) throws Exception {
		// TODO Auto-generated method stub
		if(typeName==null){
			return null;
		}
		
		List<TvContentType> result=this.getHibernateTemplate().find("from TvContentType type where UPPER(type.genre)='"+typeName.trim().toUpperCase()+"' and type.flag=1");
		if(result!=null&&result.size()>0)
			return result.get(0);
		return null;
	}

	@Override
	public List<TvContentType> getContentTypeList() throws Exception {
		// TODO Auto-generated method stub
		return this.getHibernateTemplate().find("from TvContentType type where type.flag=1");
	}

	@Override
	public void removeEPGPrograms(final String channelCode, final Date epgStartTime)
			throws Exception {
		// TODO Auto-generated method stub
		final String deleteContentStatusSql="from TvContentStatus sts where sts.contentId in" +
				"(select info.contentId from TvContentInfo info where info.channelCode=? and info.startTime>=?)";
		final String deleteContentSql="from TvContentInfo info where info.channelCode=? and info.startTime>=?";
		
		List<TvContentStatus> contentStatusList=this.getHibernateTemplate().find(deleteContentStatusSql,new Object[]{channelCode,epgStartTime});
		List<TvContentInfo> contentInfoList=this.getHibernateTemplate().find(deleteContentSql,new Object[]{channelCode,epgStartTime});
		
		log.info("delete contentStatusList size : "+contentStatusList.size());
		log.info("delete contentInfoList size : "+contentInfoList.size());
		
		this.getHibernateTemplate().deleteAll(contentStatusList);
		this.getHibernateTemplate().deleteAll(contentInfoList);
	}

	@Override
	public Date getLastProgramEndTime(String channelCode) throws Exception {
		// TODO Auto-generated method stub
		List<TvContentInfo> result=this.getHibernateTemplate().find("from TvContentInfo info where info.channelCode=? order by info.endTime desc",new Object[]{channelCode});
		
		if(result!=null&&result.size()>0)
			return result.get(0).getEndTime();
		return null;
	}

	@Override
	public List<Channel> getChannelList() {
		// TODO Auto-generated method stub
		return this.getHibernateTemplate().find("from Channel c where 1=1 order by c.code");
	}

	@Override
	public boolean isChannelExists(String channelCode) throws Exception {
		// TODO Auto-generated method stub
		List<Long> result=this.getHibernateTemplate().find("select count(*) from Channel c where c.code=? and c.status=1",new Object[]{channelCode.trim()});
		
		if(result.get(0).longValue()>0)
			return true;
		return false;
	}

	
	@Override
	public Integer deleteExpiredEpgData(final Date validDate,final int maxResults) throws Exception {
		// TODO Auto-generated method stub
		final String hsql="select tv.contentId from TvContentInfo tv where tv.endTime<=? and tv.status!=-1";
		
		return (Integer)this.getHibernateTemplate().execute(new HibernateCallback(){
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query query=session.createQuery(hsql);
				query.setTimestamp(0, validDate);
				query.setMaxResults(maxResults);
				final List<String> result=query.list();
				
				if (result != null && result.size() > 0) {
							session.doWork(new Work() {
								@Override
								public void execute(Connection connection)
										throws SQLException {
									// TODO Auto-generated method stub
									/**
									 * 以下对已经过期的节目，做删除标记
									 */
									connection.setAutoCommit(false);
									PreparedStatement pstm1 = connection
											.prepareStatement("update T_TV_CONT_INFO set STATUS=-1 where CONTID=?");
									for (String contentId : result) {
										pstm1.setString(1, contentId);
										pstm1.addBatch();
									}
									pstm1.executeBatch();
									pstm1.close();

									PreparedStatement pstm2 = connection
											.prepareStatement("update T_TV_CONTENT_INFO_STATUS set IS_VALID=0 where CONTENT_ID=?");
									for (String contentId : result) {
										pstm2.setString(1, contentId);
										pstm2.addBatch();
									}
									pstm2.executeBatch();
									pstm2.close();

									
									/**
									 * 以下对已经过期的、且SITE_ID为空的节目，直接删除
									 */
									String contentIds="";
									for (String contentId : result) {
										contentIds+="'"+contentId+"',";
									}
									contentIds=contentIds.substring(0,contentIds.length()-1);
									
									PreparedStatement pstm3 = connection
											.prepareStatement("delete from T_TV_CONT_INFO where CONTID in(select CONTENT_ID from T_TV_CONTENT_INFO_STATUS where SITE_ID is null and CONTENT_ID in("+contentIds+"))");
									int count=pstm3.executeUpdate();
									log.info("direct delete T_TV_CONT_INFO counts="+count);
									pstm3.close();
									
									PreparedStatement pstm4 = connection
											.prepareStatement("delete from T_TV_CONTENT_INFO_STATUS where SITE_ID is null and CONTENT_ID in("+contentIds+")");
									count=pstm4.executeUpdate();
									log.info("direct delete T_TV_CONTENT_INFO_STATUS counts="+count);
									pstm4.close();
									
									connection.commit();
									
								}
							});
						}
				
				return result.size();
			}
		});
	}

	@Override
	public ConfAttributeSelection getConfAttributeByKey(String key)
			throws Exception {
		// TODO Auto-generated method stub
		List<ConfAttributeSelection> result=this.getHibernateTemplate().find("from ConfAttributeSelection c where c.confKey=?",new Object[]{key});
		return result.size()==0?null:result.get(0);
	}

	@Override
	public List<TvContentInfo> getTvContentListByChannelCode(String channelCode)
			throws Exception {
		// TODO Auto-generated method stub
		return this.getHibernateTemplate().find("from TvContentInfo c where c.channelCode=?",new Object[]{channelCode.trim()});
	}

}
